<?php


namespace App\Services\Admin;


use App\Http\Controllers\Api\RemoteApiRoute;
use App\Models\WtCategory;
use App\Models\WtLowOrder;
use App\Models\WtOrderProduct;
use Illuminate\Support\Facades\Http;
use MongoDB\BSON\ObjectId;

class ProductService
{
    public function __construct()
    {

    }

    /**
     * 获取单月产品信息
     * @param $params
     * @return \Illuminate\Http\JsonResponse
     */
    public function getProductInfo($params)
    {
        $where = array();
        $where['create_time'] = ['$gt' => 0];
        $pageSize = $params['pageSize'];

        if (isset($params['domainId'])) {
            $where['domain_id'] = (int)$params['domainId'];
        }
        if (isset($params['catId']) && $params['catId'] !== '') {
            $cat_id = $params['catId'];
            $children = $this->getAllChildren($cat_id);
            $where['category_id'] = ['$in' => $children];
        }
        $where['month'] = $params['month'];
        $skip = ((int)$params['page'] - 1) * $pageSize;

        $products = WtOrderProduct::select("*")->where($where)->limit($pageSize)->skip($skip)->orderBy('order_num', 'desc')->get();
        $total = WtOrderProduct::where($where)->count();

        return Success('ok', ['items' => $products, 'total' => $total]);
    }

    /**
     * 获取月份区间内产品信息
     * @param $params
     * @return \Illuminate\Http\JsonResponse
     */
    public function getGroupProductInfo($params)
    {
        if (!$params['month_range'] || $params['month_range'] == []) {
            return Error('请选择开始结束日期！');
        }
        $month_range = $params['month_range'];
        $months = showMonthRange($month_range[0], $month_range[1]);

        //最多选择半年数据
//        if (count($months) > 6) {
//            return Error('最大月份区间为6个月！');
//        }

        //验证选择月份内数据是否已获取
        $missingMonths = [];
        foreach ($months as $month) {
            $exist = WtOrderProduct::where(['month' => $month])->first();
            if (!$exist) {
                $missingMonths[] = $month;
            }
        }
        if (count($missingMonths)) {
            return Error('以下月份数据暂未生成-' . implode(',', $missingMonths));
        }

        $where = array();
        $where['create_time'] = ['$gt' => 0];
        $pageSize = $params['pageSize'];

        if (isset($params['domainId'])) {
            $where['domain_id'] = (int)$params['domainId'];
        }
        if (isset($params['sj_name']) && $params['sj_name']) {
            $where['sj_name'] = ['$regex' => trim($params['sj_name'])];
        }
        if (isset($params['catId']) && $params['catId'] !== '') {
            $cat_id = $params['catId'];
            $children = $this->getAllChildren($cat_id);
            $where['category_id'] = ['$in' => $children];
        }
        $where['month'] = ['$in' => $months];
        $skip = ((int)$params['page'] - 1) * $pageSize;

        $pipeline = [
            [
                '$match' => $where
            ],
            [
                '$group' => [
                    '_id' => [
                        'category_id' => '$category_id',
                        'asin' => '$asin',
                    ],
                    'order_num' => ['$sum' => '$order_num'],
                    'order_counts' => ['$sum' => '$order_counts'],
                    'image' => ['$first' => '$image'],
                    'category_tree' => ['$first' => '$categories_tree'],
                    'domain_id' => ['$first' => '$domain_id'],
                    'title' => ['$first' => '$title'],
                    'group_name' => ['$first' => '$group_name'],
                    'buy_box_current' => ['$last' => '$buy_box_current'],
                    'sj_name' => ['$first' => '$sj_name'],
                    'reviews_rating' => ['$last' => '$reviews_rating'],
                    'reviews_review_count' => ['$last' => '$reviews_review_count'],
                ],
            ],
            [
                '$sort' => [
                    'order_num' => -1,
                ]
            ],
            [
                '$skip' => $skip
            ],
            [
                '$limit' => $pageSize
            ],
        ];
        $pipelineCount = [
            [
                '$match' => $where
            ],
            [
                '$group' => [
                    '_id' => [
                        'category_id' => '$category_id',
                        'asin' => '$asin',
                    ],
                    'order_num' => ['$sum' => '$order_num'],
                    'order_counts' => ['$sum' => '$order_counts'],
                    'image' => ['$first' => '$image'],
                    'title' => ['$first' => '$title'],
                    'group_name' => ['$first' => '$group_name'],
                    'buy_box_current' => ['$last' => '$buy_box_current'],
                    'sj_name' => ['$first' => '$sj_name'],
                    'reviews_rating' => ['$last' => '$reviews_rating'],
                    'reviews_review_count' => ['$last' => '$reviews_review_count'],
                ],
            ],
        ];
        $results = WtOrderProduct::aggregate($pipeline);
        $total = WtOrderProduct::aggregate($pipelineCount)->count();

        return Success('ok', ['items' => $results, 'total' => $total]);
    }

    /**
     * 获取指定月份产品线数据
     * @param $params
     * @return \Illuminate\Http\JsonResponse
     */
    public function getProductLine($params)
    {
        $where = array();
        $where['create_time'] = ['$gt' => 0];
        $pageSize = $params['pageSize'];
        $total = $params['total'];

        if (isset($params['domainId'])) {
            $where['domain_id'] = (int)$params['domainId'];
        }

        if (isset($params['catId']) && $params['catId'] !== '') {
            $cat_id = $params['catId'];
            $children = $this->getAllChildren($cat_id);
            $where['category_id'] = ['$in' => $children];
        }
        $where['month'] = $params['month'];
        $where['sj_name'] = ['$exists' => true];
        $skip = ((int)$params['page'] - 1) * $pageSize;

        //聚合获取每个产品线以及产品线下产品数量
        $pipeline = [
            [
                '$match' => $where
            ],
            [
                '$group' => [
                    '_id' => '$sj_name',
                    'line_count_total' => ['$sum' => 1],
                    'items' => [
                        '$push' => '$$ROOT',
                    ],
                ],
            ],
            [
                '$unwind' => [
                    'path' => '$items',
                    'preserveNullAndEmptyArrays' => true, // 保留空数组
                ],
            ],
            [
                '$sort' => [
                    'items.order_counts' => -1, // 按items.score降序排序
                ],
            ],
            [
                '$group' => [
                    '_id' => '$_id',
                    'line_count_total' => ['$first' => '$line_count_total'],
                    'items' => [
                        '$push' => '$items',
                    ],
                ],
            ],
            [
                '$addFields' => [
                    'top_four_items' => [
                        '$slice' => ['$items', 5], // 截取前5个元素
                    ],
                ],
            ],
            [
                '$project' => [
                    'top_four_items' => 1,
                    '_id' => 1,
                    'line_count_total' => 1,
                ],
            ],
            [
                '$sort' => [
                    'line_count_total' => -1,
                ]
            ],
            [
                '$skip' => $skip
            ],
            [
                '$limit' => $pageSize
            ],
        ];
        $results = WtOrderProduct::aggregate($pipeline);

        return Success('ok', ['items' => $results, 'total' => $total, 'catId' => $params['catId']]);
    }

    /**
     * 获取多个月份产品线数据
     * @param $params
     * @return \Illuminate\Http\JsonResponse
     */
    public function getGroupProductLine($params)
    {
        $where = array();
        $where['create_time'] = ['$gt' => 0];
        $pageSize = $params['pageSize'];
        $total = $params['total'];

        if (isset($params['domainId'])) {
            $where['domain_id'] = (int)$params['domainId'];
        }

        if (isset($params['catId']) && $params['catId'] !== '') {
            $cat_id = $params['catId'];
            $children = $this->getAllChildren($cat_id);
            $where['category_id'] = ['$in' => $children];
        }

        //月份区间
        $month_range = $params['month_range'];
        $months = showMonthRange($month_range[0], $month_range[1]);

        $where['month'] = ['$in' => $months];
        $where['sj_name'] = ['$exists' => true];
        $skip = ((int)$params['page'] - 1) * $pageSize;

        //聚合获取每个产品线以及产品线下产品数量
        $pipeline = [
            [
                '$match' => $where
            ],
            [
                '$group' => [
                    '_id' => '$sj_name',
                    'items' => [
                        '$push' => '$$ROOT',
                    ],
                ],
            ],
            [
                '$unwind' => [
                    'path' => '$items',
                    'preserveNullAndEmptyArrays' => true,
                ],
            ],
            [
                '$group' => [
                    '_id' => [
                        'sj_name' => '$_id', // 使用外部的_id，即sj_name
                        'asin' => '$items.asin',
                    ],
                    'order_counts_sum' => ['$sum' => '$items.order_counts'],
                    'order_num_sum' => ['$sum' => '$items.order_num'],
                    'image' => ['$last' => '$items.image'],
                    'categories_tree' => ['$last' => '$items.categories_tree'],
                    'title' => ['$last' => '$items.title'],
                    'group_name' => ['$last' => '$items.group_name'],
                    'buy_box_current' => ['$last' => '$items.buy_box_current'],
                    'domain_id' => ['$last' => '$items.domain_id'],
                    'sj_name' => ['$last' => '$items.sj_name'],
                    'reviews_rating' => ['$last' => '$items.reviews_rating'],
                    'reviews_review_count' => ['$last' => '$items.reviews_review_count'],
                ],
            ],
            [
                '$sort' => [
                    'order_counts_sum' => -1, // 按score_sum降序排序
                ],
            ],
            [
                '$group' => [
                    '_id' => '$_id.sj_name', // 使用外部_id的sj_name字段
                    'line_count_total' => ['$sum' => 1],
                    'top_four_items' => [
                        '$push' => [ // 将每个分组的前四个items推入数组
                            'asin' => '$_id.asin',
                            'order_counts' => '$order_counts_sum',
                            'order_num' => '$order_num_sum',
                            'image' => '$image',
                            'category_tree' => '$categories_tree',
                            'title' => '$title',
                            'group_name' => '$group_name',
                            'buy_box_current' => '$buy_box_current',
                            'domain_id' => '$domain_id',
                            'sj_name' => '$sj_name',
                            'reviews_rating' => '$reviews_rating',
                            'reviews_review_count' => '$reviews_review_count',

                        ],
                    ],
                ],
            ],
            [
                '$addFields' => [
                    'top_four_items' => [
                        '$slice' => ['$top_four_items', 5],
                    ],
                ],
            ],
            [
                '$sort' => [
                    'line_count_total' => -1,
                ],
            ],
            [
                '$skip' => $skip,
            ],
            [
                '$limit' => $pageSize,
            ],
        ];
        $results = WtOrderProduct::aggregate($pipeline);

        return Success('ok', ['items' => $results, 'total' => $total, 'catId' => $params['catId']]);
    }

    /**
     * 获取单月产品线组内产品详情
     * @param $params
     * @return \Illuminate\Http\JsonResponse
     */
    public function getProductBySjName($params)
    {
        $where = array();
        $where['month'] = $params['month'];
        $where['domain_id'] = (int)$params['domainId'];
        $where['sj_name'] = $params['sj_name'];
        if (isset($params['catId']) && $params['catId'] !== '') {
            $cat_id = $params['catId'];
            $children = $this->getAllChildren($cat_id);
            $where['category_id'] = ['$in' => $children];
        }

        //聚合获取每个产品线以及产品线下产品数量
        $pipeline = [
            [
                '$match' => $where
            ],
            [
                '$group' => [
                    '_id' => '$group_name',
                    'group_count' => ['$sum' => 1],
                    'items' => [
                        '$push' => '$$ROOT',
                    ],
                ],
            ],
            [
                '$unwind' => [
                    'path' => '$items',
                    'preserveNullAndEmptyArrays' => true, // 保留空数组
                ],
            ],
            [
                '$sort' => [
                    'items.order_counts' => -1, // 按items.score降序排序
                ],
            ],
            [
                '$group' => [
                    '_id' => '$_id',
                    'group_count' => ['$first' => '$group_count'],
                    'items' => [
                        '$push' => '$items',
                    ],
                ],
            ],
        ];
        $results = WtOrderProduct::aggregate($pipeline);

        return Success('ok', $results);
    }

    /**
     * 获取多个月产品线组内产品详情
     * @param $params
     * @return \Illuminate\Http\JsonResponse
     */
    public function getProductBySjNameGroup($params)
    {
        $where = array();
        //月份区间
        $month_range = $params['month_range'];
        $months = showMonthRange($month_range[0], $month_range[1]);

        $where['month'] = ['$in' => $months];
        $where['domain_id'] = (int)$params['domainId'];
        $where['sj_name'] = $params['sj_name'];
        if (isset($params['catId']) && $params['catId'] !== '') {
            $cat_id = $params['catId'];
            $children = $this->getAllChildren($cat_id);
            $where['category_id'] = ['$in' => $children];
        }

        //聚合获取每个产品线以及产品线下产品数量
        $pipeline = [
            [
                '$match' => $where
            ],
            [
                '$group' => [
                    '_id' => '$group_name',
                    'items' => [
                        '$push' => '$$ROOT',
                    ],
                ],
            ],
            [
                '$unwind' => [
                    'path' => '$items',
                    'preserveNullAndEmptyArrays' => true,
                ],
            ],
            [
                '$group' => [
                    '_id' => [
                        'group_name' => '$_id', // 使用外部的_id，即sj_name
                        'asin' => '$items.asin',
                    ],
                    'order_counts_sum' => ['$sum' => '$items.order_counts'],
                    'order_num_sum' => ['$sum' => '$items.order_num'],
                    'image' => ['$last' => '$items.image'],
                    'category_tree' => ['$last' => '$items.categories_tree'],
                    'title' => ['$last' => '$items.title'],
                    'group_name' => ['$last' => '$items.group_name'],
                    'buy_box_current' => ['$last' => '$items.buy_box_current'],
                    'domain_id' => ['$last' => '$items.domain_id'],
                    'sj_name' => ['$last' => '$items.sj_name'],
                    'reviews_rating' => ['$last' => '$items.reviews_rating'],
                    'reviews_review_count' => ['$last' => '$items.reviews_review_count'],
                ],
            ],
            [
                '$sort' => [
                    'order_counts_sum' => -1, // 按score_sum降序排序
                ],
            ],
            [
                '$group' => [
                    '_id' => '$_id.group_name',
                    'group_count' => ['$sum' => 1],
                    'items' => [
                        '$push' => [ // 将每个分组的前四个items推入数组
                            'asin' => '$_id.asin',
                            'order_counts' => '$order_counts_sum',
                            'order_num' => '$order_num_sum',
                            'image' => '$image',
                            'category_tree' => '$category_tree',
                            'title' => '$title',
                            'group_name' => '$group_name',
                            'buy_box_current' => '$buy_box_current',
                            'domain_id' => '$domain_id',
                            'sj_name' => '$sj_name',
                            'reviews_rating' => '$reviews_rating',
                            'reviews_review_count' => '$reviews_review_count',

                        ],
                    ],
                ],
            ],
            [
                '$sort' => [
                    'group_count' => -1, // 按score_sum降序排序
                ],
            ],
        ];
        $results = WtOrderProduct::aggregate($pipeline);

        return Success('ok', $results);
    }

    /**
     * 获取指定月份日均单量
     * @param $params
     * @return \Illuminate\Http\JsonResponse
     */
    public function getDayAvgOrder($params)
    {
        $where = array();
        //月份区间
        $month_range = $params['month_range'];
        $day_order = $params['avg_num'];

        list($year_start, $month_start) = explode('-', $month_range[0]);
        list($year_end, $month_end) = explode('-', $month_range[1]);
        $last_year_month_start = date('Y-m', strtotime("-1 year $year_start-$month_start-01"));
        $last_year_month_end = date('Y-m', strtotime("-1 year $year_end-$month_end-01"));
        $months_1 = showMonthRange($month_range[0], $month_range[1]);
        $months_2 = showMonthRange($last_year_month_start, $last_year_month_end);
        $months = array_merge($months_2, $months_1);

        $month_days = [];
        foreach ($months as $month) {
            $month_days[$month] = date('t', strtotime($month));
        }
        $where['month'] = ['$in' => $months];
        $where['domain_id'] = (int)$params['domainId'];
        if ($params['root_cate']){
            $where['root_cate'] = $params['root_cate'];
        }

        $pipeline = [
            [
                '$match' => $where
            ],
            [
                '$sort' => [
                    'month' => -1
                ],
            ],
            [
                '$group' => [
                    '_id' => '$asin',
                    'sj_name' => ['$first' => '$sj_name'],
                    'image' => ['$first' => '$image'],
                    'domain_id' => ['$first' => '$domain_id'],
                    'category_id' => ['$first' => '$category_id'],
                    'group_name' => ['$first' => '$group_name'],
                    'categories_tree' => ['$first' => '$categories_tree'],
                    'root_cate' => ['$first' => '$root_category'],
                    'items' => [
                        '$push' => [
                            'month' => '$month',
                            'order_num' => '$order_num',
                        ],
                    ],
                ],
            ],
            [
                '$addFields' => [
                    'current' => [
                        '$filter' => [
                            'input' => '$items',
                            'as' => 'item',
                            'cond' => [
                                '$gte' => [
                                    '$$item.month',
                                    $month_range[0]
                                ]
                            ]
                        ]
                    ]
                ]
            ],
            [
                '$addFields' => [
                    'last' => [
                        '$filter' => [
                            'input' => '$items',
                            'as' => 'item',
                            'cond' => [
                                '$lt' => [
                                    '$$item.month',
                                    $month_range[0]
                                ]
                            ]
                        ]
                    ]
                ]
            ],
            [
                '$group' => [
                    '_id' => '$sj_name',
                    'asin_data' => [
                        '$push' => [
                            '_id' => '$_id',
                            'sj_name' => '$sj_name',
                            'image' => '$image',
                            'domain_id' => '$domain_id',
                            'category_id' => '$category_id',
                            'group_name' => '$group_name',
                            'categories_tree' => '$categories_tree',
                            'root_cate' => '$root_cate',
                            'items' => '$items',
                            'current' => '$current',
                            'last' => '$last',
                            'avg_num' => '$avg_num',
                        ]
                    ],
                    'total' => [
                        '$push' => '$current'
                    ]
                ]
            ],
            [
                '$unwind' => '$total'
            ],
            [
                '$unwind' => '$total'
            ],
            [
                '$group' => [
                    '_id' => [
                        'sj_name' => '$_id',
                        'month' => '$total.month'
                    ],
                    'order_num_sum' => [
                        '$sum' => '$total.order_num'
                    ],
                    'asin_data' => ['$first' => '$asin_data']
                ]
            ],
            [
                '$group' => [
                    '_id' => '$_id.sj_name',
                    'total' => [
                        '$push' => [
                            'month' => '$_id.month',
                            'order_num' => '$order_num_sum'
                        ]
                    ],
                    'asin_data' => ['$first' => '$asin_data']
                ]
            ],
            [
                '$unwind' => '$asin_data'
            ],
            [
                '$replaceRoot' => [
                    'newRoot' => [
                        '$mergeObjects' => [
                            '$asin_data',
                            [
                                'average_order' => '$total'
                            ]
                        ]
                    ]
                ]
            ],
            [
                '$match' => [
                    'current' => [
                        '$elemMatch' => [
                            'month' => $month_range[1],
                            'order_num' => ['$gte' => $day_order * $month_days[$month_range[1]]]
                        ],
                    ],
                ],
            ],
            [
                '$addFields' => [
                    // 计算当月订单数的最大值，用于排序
                    'avg_num' => [
                        '$divide' => [
                            [
                                '$max' => [
                                    '$map' => [
                                        'input' => '$items',
                                        'as' => 'item',
                                        'in' => [
                                            '$cond' => [
                                                ['$eq' => ['$$item.month', $month_range[1]]],
                                                '$$item.order_num',
                                                null
                                            ]
                                        ]
                                    ]
                                ]
                            ],
                            ['$toInt' => $month_days[$month_range[1]]]
                        ]
                    ],
                ]
            ],

            [
                '$sort' => [
                    'avg_num' => -1,
                ],
            ],

        ];
        $results = WtOrderProduct::raw(function ($collection) use ($pipeline) {
            return $collection->aggregate($pipeline, ['allowDiskUse' => true]);
        });
        return Success('ok', $results);
    }

    /**
     * 获取指定月份日均单量
     * @param $params
     * @return \Illuminate\Http\JsonResponse
     */
    public function productLineTotalAnalyse($params)
    {
        $pageSize = $params['pageSize'];
        $skip = ((int)$params['page'] - 1) * $pageSize;
        $where = array();
        //月份区间
        $month_range = $params['month_range'];
        list($year_start, $month_start) = explode('-', $month_range[0]);
        list($year_end, $month_end) = explode('-', $month_range[1]);
        $months_1 = showMonthRange($month_range[0], $month_range[1]);

        //环比月份
        $last_year_month_start = date('Y-m', strtotime("-1 year $year_start-$month_start-01"));
        $last_year_month_end = date('Y-m', strtotime("-1 year $year_end-$month_end-01"));
        $months_2 = showMonthRange($last_year_month_start, $last_year_month_end);

        //同比月份
        $month_count = count($months_1);
        $current_year_month_start = date('Y-m', strtotime("-$month_count month $month_range[0]-01"));
        $current_year_month_end = date('Y-m', strtotime("-$month_count month $month_range[1]-01"));
        $months_3 = showMonthRange($current_year_month_start, $current_year_month_end);
        $months = array_merge($months_2, $months_1, $months_3);

        //获取汇总数据
        $where['month'] = ['$in' => $months];
        $where['domain_id'] = (int)$params['domainId'];
        $where['sj_name'] = ['$exists' => true, '$ne' => ''];
        if ($params['root_cate']){
            $where['root_cate'] = $params['root_cate'];
        }

        $pipeline = [
            // 过滤特定月份的数据
            [
                '$match' => $where
            ],
            // 根据 sj_name 和 month 进行初步分组
            [
                '$group' => [
                    '_id' => [
                        'sj_name' => '$sj_name',
                        'month' => '$month',
                        'group_name' => '$group_name'
                    ],
                    'asin_count' => ['$addToSet' => '$asin'],
                    'domain_id' => ['$first' => '$domain_id'],
                    'categories_tree' => ['$addToSet' => '$categories_tree'], // 使用 addToSet 来累积不同的 categories_tree
                    'root_cate' => ['$addToSet' => '$root_cate'], // 使用 addToSet 来累积不同的 categories_tree
                    'total_order_num' => ['$sum' => '$order_num'],
                    'items' => [
                        '$push' => [
                            'asin' => '$asin',
                            'order_num' => '$order_num',
                        ]
                    ]
                ]
            ],
            // 计算 asin 数量
            [
                '$addFields' => [
                    'asin_count' => ['$size' => '$asin_count'],
                    'domain_id' => '$domain_id'
                ]
            ],
            // 进一步分组以计算每个 sj_name 和 month 的数据
            [
                '$group' => [
                    '_id' => [
                        'sj_name' => '$_id.sj_name',
                        'month' => '$_id.month'
                    ],
                    'domain_id' => ['$first' => '$domain_id'],
                    'group_orders' => [
                        '$push' => [
                            'group_name' => '$_id.group_name',
                            'asin_count' => '$asin_count',
                            'total_order_num' => '$total_order_num',
                            'categories_tree' => '$categories_tree',
                            'root_cate' => '$root_cate',
                            'items' => '$items'
                        ]
                    ],
                    'categories_tree' => ['$first' => '$categories_tree'],
                    'root_cate' => ['$first' => '$root_cate'],
                    'total_order_num' => ['$sum' => '$total_order_num'],
                    'asin_count' => ['$sum' => '$asin_count']
                ]
            ],
            // 进一步分组以计算每个 sj_name 的数据
            [
                '$group' => [
                    '_id' => '$_id.sj_name',
                    'domain_id' => ['$first' => '$domain_id'],
                    'data' => [
                        '$push' => [
                            'month' => '$_id.month',
                            'group_orders' => '$group_orders',
                            'total_order_num' => '$total_order_num',
                            'asin_count' => '$asin_count'
                        ]
                    ],
                    'categories_tree' => ['$first' => '$categories_tree'],
                    'root_cate' => ['$first' => '$root_cate']
                ]
            ],
            // 将同比、环比月份的数据分别处理
            [
                '$addFields' => [
                    'current_period' => [
                        '$filter' => [
                            'input' => '$data',
                            'as' => 'item',
                            'cond' => ['$in' => ['$$item.month', $months_1]]
                        ]
                    ],
                    'same_period_last_year' => [
                        '$filter' => [
                            'input' => '$data',
                            'as' => 'item',
                            'cond' => ['$in' => ['$$item.month', $months_2]]
                        ]
                    ],
                    'previous_period' => [
                        '$filter' => [
                            'input' => '$data',
                            'as' => 'item',
                            'cond' => ['$in' => ['$$item.month', $months_3]]
                        ]
                    ]
                ]
            ],
            // 添加一个阶段来计算 current_period 中 total_order_num 的总和
            [
                '$addFields' => [
                    'current_period_total_order_num' => [
                        '$reduce' => [
                            'input' => '$current_period',
                            'initialValue' => 0,
                            'in' => [
                                '$add' => ['$$value', '$$this.total_order_num']
                            ]
                        ]
                    ]
                ]
            ],
            [
              '$match' => ['current_period_total_order_num' => ['$gt' => 0]]
            ],
            // 按照 current_period_total_order_num 进行排序
            [
                '$sort' => [
                    'current_period_total_order_num' => -1
                ]
            ],

            [
                '$skip' => $skip
            ],
            [
                '$limit' => $pageSize
            ]
        ];
        $where['month'] = ['$in' => $months_1];
        $pipelineCount = [
            [
                '$match' => $where
            ],
            [
                '$group' => [
                    '_id' => '$sj_name',
                ],
            ],
            ['$count' => 'total']
        ];

        $totals = WtOrderProduct::raw(function ($collection) use ($pipelineCount) {
            return $collection->aggregate($pipelineCount, ['allowDiskUse' => true]);
        });
        $totalCount = $totals[0]['total'] ?? 0;

        // 执行聚合查询，并设置 allowDiskUse 选项
        $results = WtOrderProduct::raw(function ($collection) use ($pipeline) {
            return $collection->aggregate($pipeline, ['allowDiskUse' => true]);
        });

        $arr = [];
        foreach ($results as $result) {
            $current_asin_count = 0;
            $current_order_count = 0;
            $same_asin_count = 0;
            $same_order_count = 0;
            $last_asin_count = 0;
            $last_order_count = 0;

            //当前单量等数据
            $current_group_data = [];
            $group_top_product = [];
            if (count($result->current_period)) {
                foreach ($result->current_period as $item) {
                    $current_order_count += $item->total_order_num;
                    $current_asin_count += $item->asin_count;

                    //组单量及其占比
                    foreach ($item->group_orders as $order) {
                        if (!isset($current_group_data[$order->group_name])) {
                            $current_group_data[$order->group_name] = 0;
                        }
                        $current_group_data[$order->group_name] += $order->total_order_num;

                        //获取头部产品(每组一个)
                        if (!isset($group_top_product[$order->group_name])) {
                            $group_top_product[$order->group_name] = [];
                        }
                        foreach ($order->items as $product) {
                            if (!isset($group_top_product[$order->group_name][$product->asin])) {
                                $group_top_product[$order->group_name][$product->asin] = 0;
                            }
                            $group_top_product[$order->group_name][$product->asin] += $product->order_num;
                        }
                    }
                }
            }
            $top_product = [];
            foreach ($group_top_product as $k => $v) {
                arsort($v);
                $firstKey = key($v);
                $firstValue = reset($v);
                $top_product[] = [
                    'group_name' => $k,
                    'asin' => $firstKey,
                    'order_num' => $firstValue
                ];
            }
            usort($top_product, function ($a, $b) {
                return $b['order_num'] - $a['order_num'];
            });
            uasort($current_group_data, function ($a, $b) {
                return $b <=> $a;
            });
            $current_desc = [];
            if ($current_order_count > 0) {
                foreach ($current_group_data as $k => $v) {
                    $current_desc[] = [
                        'name' => $k,
                        'order_num' => $v,
                        'percent' => number_format($v * 100 / $current_order_count, 2) . '%',
                    ];
                }
            }

            //同比单量等数据
            $same_group_data = [];
            if (count($result->previous_period)) {
                foreach ($result->previous_period as $item) {
                    $same_order_count += $item->total_order_num;
                    $same_asin_count += $item->asin_count;

                    //组单量及其占比
                    foreach ($item->group_orders as $order) {
                        if (!isset($same_group_data[$order->group_name])) {
                            $same_group_data[$order->group_name] = 0;
                        }
                        $same_group_data[$order->group_name] += $order->total_order_num;
                    }
                }
            }
            uasort($same_group_data, function ($a, $b) {
                return $b <=> $a;
            });
            $same_desc = [];
            if ($same_order_count > 0) {
                foreach ($same_group_data as $k => $v) {
                    $same_desc[] = [
                        'name' => $k,
                        'order_num' => $v,
                        'percent' => number_format($v * 100 / $same_order_count, 2) . '%',
                    ];
                }
            }

            //单量同比百分比
            $same_percent = [];
            foreach ($current_group_data as $ck => $cv){
                if (isset($same_group_data[$ck]) && $same_group_data[$ck] > 0){
                    $same_percent[] = [
                        'name' => $ck,
                        'percent' => number_format($cv * 100 / $same_group_data[$ck], 2) . '%',
                    ];
                }
            }

            //环比单量等数据
            $last_group_data = [];
            if (count($result->same_period_last_year)) {
                foreach ($result->same_period_last_year as $item) {
                    $last_order_count += $item->total_order_num;
                    $last_asin_count += $item->asin_count;

                    //组单量及其占比
                    foreach ($item->group_orders as $order) {
                        if (!isset($last_group_data[$order->group_name])) {
                            $last_group_data[$order->group_name] = 0;
                        }
                        $last_group_data[$order->group_name] += $order->total_order_num;
                    }
                }
            }

            uasort($last_group_data, function ($a, $b) {
                return $b <=> $a;
            });
            $last_desc = [];
            if ($last_order_count > 0) {
                foreach ($last_group_data as $k => $v) {
                    $last_desc[] = [
                        'name' => $k,
                        'order_num' => $v,
                        'percent' => number_format($v * 100 / $last_order_count, 2) . '%'
                    ];
                }
            }

            $arr[] = [
                'sj_name' => $result->_id,
                'categories_tree' => (array)$result->categories_tree,
                'root_cate' => (array)$result->root_cate,
                'order_total' => $current_order_count . '/' . $same_order_count . '/' . $last_order_count,
                'asin_count' => $current_asin_count . '/' . $same_asin_count . '/' . $last_asin_count,
                'current_desc' => $current_desc,
                'group_count' => count($current_desc),
                'same_desc' => $same_desc,
                'last_desc' => $last_desc,
                'same_percent' => $same_percent,
                'top_product' => $top_product,
                'domain_id' => $result->domain_id,
            ];
        }

        return Success('ok', ['items' => $arr, 'total' => $totalCount]);
    }

    public function updateAsinOrder($params){
        $url = env('REMOTE_API_URL') . RemoteApiRoute::GET_ASIN_ORDER;
        $months = get_last_two_month($params['month']);
        $params['month'] = $months;
        $response = Http::get($url, $params);

        if ($response->ok()) {
            $ret = $response->json();

            //更新低订单表的单量
            $update = [
              'order_num' => $ret['result'][0],
              'last_order' => $ret['result'][1],
              'last_last_order' => $ret['result'][2],
            ];
            WtLowOrder::where(['_id' => new ObjectId($params['_id'])])->update(['$set' => $update]);

            return $ret;
        } else {
            return Error($response->reason());
        }
    }

    /**
     * 导出表格
     * @param $params
     * @return \Illuminate\Http\JsonResponse
     */
    public function exportXls($params){
        $where_str = '';
        foreach ($params as $k => $v){
            if ($k == 'month_range'){
                $where_str .= $k.'='.implode('|',$v).',';
            }elseif($k == 'root_cate'){
                $where_str .= $k.'='.base64_encode($v).',';
            }elseif($k == 'month'){
                $months = get_last_two_month($v);
                $where_str .= $k.'='.implode('|',$months).',';
            }else{
                $where_str .= $k.'='.$v.',';
            }
        }
        exec('python wt_export.py ' . urlencode($where_str) . ' 2>&1', $out, $res);
        if ($res == 0){
            return Success('ok', $out[0]);
        }else{
            return Error('导出失败，请稍后重试！',$res,$out);
        }
    }

    /**
     * 获取所有子分类ID
     * @param $catId
     * @return mixed
     */
    private function getAllChildren($catId)
    {
        $ids = [];
        $this->getAllSubcategoriesIds((int)$catId, $ids);
        return $ids;
    }

    // 递归函数，用于获取给定分类ID的所有子分类ID
    private function getAllSubcategoriesIds($catId, &$ids)
    {
        // 将当前分类ID存入结果数组中
        $ids[] = $catId;

        $category = WtCategory::where('catId', $catId)->first();
        if ($category && $category->children_num > 0) {
            $cat_arr = explode(',', $category->children_str);
            foreach ($cat_arr as $value) {
                // 递归调用，获取每个子分类的所有下级分类ID，并将其存入结果数组中
                $this->getAllSubcategoriesIds((int)$value, $ids);
            }
        }
    }
}
